package sql;

import java.sql.*;

import java.util.ArrayList;

import data.CMSRes;
import data.account.Account;
import data.account.admin.AdminAccount;
import data.account.attendee.AttendeeAccount;
import data.conference.Conference;

public class SQLInterface
{
	private static Connection conn = null;
	
	private SQLInterface() { }
	
	public static void initDatabase()
	{
		try
        {
            String userName = "root";
            String password = "test1234";
            String url = "jdbc:mysql://localhost/cms_database";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
    		CMSRes.setConference(getConference());
        }
        catch (Exception e)
        {
        	e.printStackTrace();
            System.err.println ("Cannot connect to database server");
        }
	}
	
	public static Account attemptLogin(String username, String password)
	{
		try {
			Statement statement = conn.createStatement();
			String query = "SELECT isAdmin, username, password, data FROM accounts WHERE username = '"+username+"' and password = '"+password+"'";
			ResultSet accounts = statement.executeQuery(query);
			if(accounts.next())
			{
				String isAdmin = accounts.getString("isAdmin");
				String data = accounts.getString("data");
				if(isAdmin.equals("true"))
					return new AdminAccount();
				else
					return new AttendeeAccount(username, password, data);
			}
		} catch (SQLException e) { e.printStackTrace(); }
		return null;
	}
	
	public static Account attemptRegister(String username, String password)
	{
		try {
			Statement statement = conn.createStatement();
			String query = "SELECT isAdmin, username, password, data FROM accounts WHERE username = '"+username+"'";
			ResultSet accounts = statement.executeQuery(query);
			if(accounts.next())
				return null;
			AttendeeAccount attendee = new AttendeeAccount(username, password);
			createAttendee(attendee);
			return attendee;
		} catch (SQLException e) { e.printStackTrace(); }
		
		return null;
	}
	
	public static AttendeeAccount getAttendee(String username)
	{
		try {
			Statement statement = conn.createStatement();
			String query = "SELECT isAdmin, username, password, data FROM accounts WHERE username = '"+username+"'";
			ResultSet accounts = statement.executeQuery(query);
			if(accounts.next())
			{
				String isAdmin = accounts.getString("isAdmin");
				String password = accounts.getString("password");
				String data = accounts.getString("data");
				if(isAdmin.equals("true"))
					return null;
				else
					return new AttendeeAccount(username, password, data);
			}
		} catch (SQLException e) { e.printStackTrace(); }
		return null;
	}
	
	public static void createAttendee(AttendeeAccount account)
	{
		try
		{
			Statement statement = conn.createStatement();
			String query = "INSERT INTO accounts VALUES (null,'false',"+"'"+account.userID()+"','"+ account.password()+"','"+account.toData()+"')";
			int updateQuery = statement.executeUpdate(query);
			if (updateQuery != 0) { System.out.println("account saved successfully"); }
			else System.out.println();
	    }
		catch(Exception e) { e.printStackTrace(); }
	}
	
	public static void saveAttendee(AttendeeAccount account)
	{
		try
		{
			Statement statement = conn.createStatement();
			String query = "UPDATE accounts SET data = '"+account.toData()+"'  WHERE username = '"+account.userID()+"' and password = '"+account.password()+"'";
			int updateQuery = statement.executeUpdate(query);
			if (updateQuery != 0) { System.out.println("account saved successfully"); }
			else System.out.println();
	    }
		catch(Exception e) { e.printStackTrace(); }
	}
	
	public static void deleteAccount(AttendeeAccount account)
	{
		try
		{
			Statement statement = conn.createStatement();
			String query = "DELETE FROM accounts WHERE username = '"+account.userID()+"' and password = '"+account.password()+"'";
			int updateQuery = statement.executeUpdate(query);
			if (updateQuery != 0) { System.out.println("account deleted successfully"); }
	    }
		catch(Exception e) { e.printStackTrace(); }
	}
	
	public static ArrayList<AttendeeAccount> getAttendeeList()
	{
		System.out.println("Retrieving attendee list!");
		ArrayList<AttendeeAccount> attendeeList = new ArrayList<AttendeeAccount>();
		try {
			Statement statement = conn.createStatement();
			String query = "SELECT isAdmin, username, password, data FROM accounts WHERE isAdmin NOT IN ('true')";
			ResultSet accounts = statement.executeQuery(query);
			while(accounts.next())
			{
				System.out.println("Has account!");
				String username = accounts.getString("username");
				String password = accounts.getString("password");
				String data = accounts.getString("data");
				System.out.println("Account: " + username);
				attendeeList.add(new AttendeeAccount(username, password, data));
			}
		} catch (SQLException e) { e.printStackTrace(); }
		return attendeeList;
	}
	
	public static Conference getConference()
	{
		try {
			Statement statement = conn.createStatement();
			String query = "SELECT data FROM conference";
			ResultSet accounts = statement.executeQuery(query);
			if(accounts.next())
			{
				String data = accounts.getString("data");
				System.out.println("Conference data: [" + data + "]");
				return new Conference(data);
			}
		} catch (SQLException e) { e.printStackTrace(); }
		return new Conference("A Conference", "A standard conference.");
	}
	
	public static void saveConference(Conference conference)
	{
		try
		{
			Statement statement = conn.createStatement();
			String query = "UPDATE conference SET data = '"+conference.toData()+"'  WHERE conferenceID = '1'";
			int updateQuery = statement.executeUpdate(query);
			if (updateQuery != 0) { System.out.println("account saved successfully"); }
	    }
		catch(Exception e) { e.printStackTrace(); }
	}
}